﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using OAWeb.Models;
using System.IO;

namespace OAWeb.Controllers
{
    public class GradesController : Controller
    {
        private OADbContext db = new OADbContext();

        // GET: Grades
        public ActionResult Index()
        {
            var grades = db.Grades.Include(g => g.Course).Include(g => g.Exam).Include(g => g.Student);
            return View(grades.ToList());
        }

        // GET: Grades/Details/5
        public ActionResult Details(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Grade grade = db.Grades.Find(id);
            if (grade == null)
            {
                return HttpNotFound();
            }
            return View(grade);
        }

        // GET: Grades/Create
        public ActionResult Create()
        {
           ViewBag.CourseID = new SelectList(db.Courses, "ID", "Subject");
            ViewBag.ExamID = new SelectList(db.Exams, "ID", "ExamName");
           // ViewBag.StudentID = new SelectList(db.Students, "ID", "Number");
            return View();
        }

        // POST: Grades/Create
        // 为了防止“过多发布”攻击，请启用要绑定到的特定属性，有关 
        // 详细信息，请参阅 https://go.microsoft.com/fwlink/?LinkId=317598。
        [HttpPost]
        [ValidateAntiForgeryToken]
        /*public ActionResult Create([Bind(Include = "ID,StudentID,ExamID,CourseID,Score,ClassRank,GradeRank")] Grade grade)
        {
            if (ModelState.IsValid)
            {
                db.Grades.Add(grade);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            ViewBag.CourseID = new SelectList(db.Courses, "ID", "Subject", grade.CourseID);
            ViewBag.ExamID = new SelectList(db.Exams, "ID", "ExamName", grade.ExamID);
            ViewBag.StudentID = new SelectList(db.Students, "ID", "Number", grade.StudentID);
            return View(grade);
        }*/
        public ActionResult Create([Bind(Include = "ID,CourseID,ExamID,Score")] Grade grade,int cid)
        {
            /* List<Course> Courses = db.Courses.ToList();
             if (ModelState.IsValid)
             {
                 foreach (var course in Courses)
                 {
                     grade.CourseID = course.ID;
                     db.Grades.Add(grade);

                     db.SaveChanges();
                 }

                 return RedirectToAction("Index");
             }
             */
            List<Student> Students = db.Students.Where(s=>s.TeachingClassID==cid).ToList();
            if (ModelState.IsValid)
            {
                foreach (var student in Students)
                {
                    grade.StudentID = student.ID;
                    db.Grades.Add(grade);

                    db.SaveChanges();
                }

                
            }
            ViewBag.CourseID = new SelectList(db.Courses, "ID", "Subject", grade.CourseID);
            ViewBag.ExamID = new SelectList(db.Exams, "ID", "ExamName", grade.ExamID);
            ViewBag.StudentID = new SelectList(db.Students, "ID", "Number", grade.StudentID);
            return View(grade);
        }
        // GET: Grades/Edit/5
        public ActionResult Edit(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Grade grade = db.Grades.Find(id);
            if (grade == null)
            {
                return HttpNotFound();
            }
            ViewBag.CourseID = new SelectList(db.Courses, "ID", "Subject", grade.CourseID);
            ViewBag.ExamID = new SelectList(db.Exams, "ID", "ExamName", grade.ExamID);
            ViewBag.StudentID = new SelectList(db.Students, "ID", "Number", grade.StudentID);
            return View(grade);
        }

        // POST: Grades/Edit/5
        // 为了防止“过多发布”攻击，请启用要绑定到的特定属性，有关 
        // 详细信息，请参阅 https://go.microsoft.com/fwlink/?LinkId=317598。
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit([Bind(Include = "ID,StudentID,ExamID,CourseID,Score")] Grade grade)
        {
            if (ModelState.IsValid)
            {
                db.Entry(grade).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            ViewBag.CourseID = new SelectList(db.Courses, "ID", "Subject", grade.CourseID);
            ViewBag.ExamID = new SelectList(db.Exams, "ID", "ExamName", grade.ExamID);
            ViewBag.StudentID = new SelectList(db.Students, "ID", "Number", grade.StudentID);
            return View(grade);
        }

        // GET: Grades/Delete/5
        public ActionResult Delete(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            Grade grade = db.Grades.Find(id);
            db.Grades.Remove(grade);
            db.SaveChanges();
            return RedirectToAction("Index");
            if (grade == null)
            {
                return HttpNotFound();
            }
            return View(grade);
        }

        // POST: Grades/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult DeleteConfirmed(int id)
        {
            Grade grade = db.Grades.Find(id);
            db.Grades.Remove(grade);
            db.SaveChanges();
            return RedirectToAction("Index");
        }
        public ActionResult ListByScore(int id1, int? id2 = 1, int? id3 = 0,int? id4=1)
        {

            List<ScoreEdit> scoreedit = db.Grades.Where(g => g.Student.TeachingClassID == id1 && g.ExamID == id2 && g.CourseID == id4).Select(g => new ScoreEdit { StudentID = g.StudentID, ID = g.ID,CourseName=g.Course.Subject,StuName=g.Student.Name, ExamID = g.ExamID, Score = g.Score }).ToList();
            return View(scoreedit);

        }

        [HttpPost]
        public ActionResult ListByScore(List<ScoreEdit> ScoreEdit)
        {
            foreach (var m in ScoreEdit)
            {
                //int gid = db.Grades.Single(g => g.StudentID == m.StudentID && g.CourseID == m.CourseID&&g.ExamID== m.ExamID).ID;
                var t = db.Grades.Find(m.ID);
                t.Score = m.Score;
            }
            db.SaveChanges();
            return RedirectToAction("Index" ,"Score", new { examid = 1 });
        }


       
        public ActionResult Information_Depart()
        {
            return View();
        }
        public ActionResult Information_DeparGra(int id)
        {

            var departmentGrade = db.DepartmentGrades.Where(d => d.DepartmentID == id).ToArray();

            int[] idItems = new int[departmentGrade.Length];
            string[] nameItems = new string[departmentGrade.Length];
            string[] leaderItems = new string[departmentGrade.Length];
            for (int i = 0; i < departmentGrade.Length; i++)
            {
                nameItems[i] = departmentGrade[i].Name;
                idItems[i] = departmentGrade[i].ID;
                leaderItems[i] = departmentGrade[i].Leader;
            }
            ViewBag.idItems = idItems;
            ViewBag.nameItems = nameItems;
            ViewBag.leaderItems = leaderItems;
            return View();
        }
        public ActionResult Information_Class(int id)
        {

            var teachingClasses = db.TeachingClasses.Where(t => t.DepartmentGradeID == id).ToArray();
            int[] idItems = new int[teachingClasses.Length];
            for (int i = 0; i < teachingClasses.Length; i++)
            {
                idItems[i] = teachingClasses[i].TeachingClassID;
            }
            ViewBag.idItems = idItems;
            return View(teachingClasses);
        }
        public ActionResult ExamChose()
        {
            List<Exam> exam = db.Exams.ToList();

            return View(exam);
        }
        public ActionResult ScoreShowc(int id)
        {
            ViewBag.examid = id;
            var exams = db.Exams.Single(g => g.ID == id);

            List<TeachingClass> teachingClasses = db.TeachingClasses.Where(t => t.DepartmentGradeID == exams.DepartmentGradeID).ToList();

            return View(teachingClasses);
        }

        public ActionResult CourseChose(int classid,int examid)
        {
            ViewBag.classid = classid;
            ViewBag.examid = examid;
            return View();
        }
        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
        public FileResult ExportStu2()
        {
            //获取list数据
            var checkList = (from grade in db.Grades

                             select new
                             {
                                 grade_subject = grade.Course.Subject,
                                 grade_examname = grade.Exam.ExamName,
                                 grade_number = grade.Student.Number,
                                 grade_score = grade.Score
                                

                             }).ToList();

            //创建Excel文件的对象
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet
            NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");

            //貌似这里可以设置各种样式字体颜色背景等，但是不是很方便，这里就不设置了

            //给sheet1添加第一行的头部标题
            NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
            row1.CreateCell(0).SetCellValue("科目");
            row1.CreateCell(1).SetCellValue("考试名称");
            row1.CreateCell(2).SetCellValue("学号");
            row1.CreateCell(3).SetCellValue("分数");
           
            //....N行

            //将数据逐步写入sheet1各个行
            for (int i = 0; i < checkList.Count; i++)
            {
                NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
                rowtemp.CreateCell(0).SetCellValue(checkList[i].grade_subject.ToString());
                rowtemp.CreateCell(1).SetCellValue(checkList[i].grade_examname.ToString());
                rowtemp.CreateCell(2).SetCellValue(checkList[i].grade_number.ToString());
                rowtemp.CreateCell(3).SetCellValue(checkList[i].grade_score.ToString());
               
                //....N行
            }
            // 写入到客户端
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            DateTime dt = DateTime.Now;
            string dateTime = dt.ToString("yyMMddHHmmssfff");
            string fileName = "查询结果" + dateTime + ".xls";
            return File(ms, "application/vnd.ms-excel", fileName);
        }
    }
}
